using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Fieldbook.Entities;
using Fieldbook.Common;
namespace Fieldbook.DAL
{
	public static class AppuserDAO
	{
		#region Insert
		private static DbCommand GetInsertCommand(SqlDatabase db)
		{
            string insertSQL = "INSERT INTO dbo.AppUser (ROLEID, USERNAME, PASSWORD, JMBG, FIRSTNAME, LASTNAME, STREETNAME, STREETNUMBER, POSTALCODE, CITY, EMAILADRESS, RECEIVEMAIL, PHONENO, BIRTHDATE, STATUS) VALUES (@ROLEID, @USERNAME, @PASSWORD, @JMBG, @FIRSTNAME, @LASTNAME, @STREETNAME, @STREETNUMBER, @POSTALCODE, @CITY, @EMAILADRESS, @RECEIVEMAIL, @PHONENO, @BIRTHDATE, @STATUS) SET @USERID = SCOPE_IDENTITY()";
            DbCommand insertCommand = db.GetSqlStringCommand(insertSQL);
            db.AddOutParameter(insertCommand, "@USERID", DbType.Int64, 8);
            db.AddInParameter(insertCommand, "@ROLEID", DbType.Int32);
            db.AddInParameter(insertCommand, "@USERNAME", DbType.String);
            db.AddInParameter(insertCommand, "@PASSWORD", DbType.String);
            db.AddInParameter(insertCommand, "@JMBG", DbType.String);
            db.AddInParameter(insertCommand, "@FIRSTNAME", DbType.String);
            db.AddInParameter(insertCommand, "@LASTNAME", DbType.String);
            db.AddInParameter(insertCommand, "@STREETNAME", DbType.String);
            db.AddInParameter(insertCommand, "@STREETNUMBER", DbType.String);
            db.AddInParameter(insertCommand, "@POSTALCODE", DbType.String);
            db.AddInParameter(insertCommand, "@CITY", DbType.String);
            db.AddInParameter(insertCommand, "@EMAILADRESS", DbType.String);
            db.AddInParameter(insertCommand, "@RECEIVEMAIL", DbType.Boolean);
            db.AddInParameter(insertCommand, "@PHONENO", DbType.String);
            db.AddInParameter(insertCommand, "@BIRTHDATE", DbType.DateTime);
            db.AddInParameter(insertCommand, "@STATUS", DbType.Int32);
            return insertCommand;
		}
		private static void SetInsertValues(SqlDatabase db, DbCommand cmd, Appuser entity)
		{
			db.SetParameterValue(cmd, "@ROLEID", entity.Roleid);
			db.SetParameterValue(cmd, "@USERNAME", entity.Username);
			db.SetParameterValue(cmd, "@PASSWORD", entity.Password);
			db.SetParameterValue(cmd, "@JMBG", entity.Jmbg);
			db.SetParameterValue(cmd, "@FIRSTNAME", entity.Firstname);
			db.SetParameterValue(cmd, "@LASTNAME", entity.Lastname);
			db.SetParameterValue(cmd, "@STREETNAME", entity.Streetname);
			db.SetParameterValue(cmd, "@STREETNUMBER", entity.Streetnumber);
			db.SetParameterValue(cmd, "@POSTALCODE", entity.Postalcode);
			db.SetParameterValue(cmd, "@CITY", entity.City);
			db.SetParameterValue(cmd, "@EMAILADRESS", entity.Emailadress);
            db.SetParameterValue(cmd, "@RECEIVEMAIL", entity.ReceiveMail);
			db.SetParameterValue(cmd, "@PHONENO", entity.Phoneno);
			db.SetParameterValue(cmd, "@BIRTHDATE", entity.Birthdate);
			db.SetParameterValue(cmd, "@STATUS", entity.Statusid);
		}
		public static bool InsertAppuser(Appuser entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			if (res)
			{
				entity.Userid = (long?)db.GetParameterValue(cmd, "@USERID");
			}
			return res;
		}
		public static bool InsertAppuser(Appuser entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			if (res)
			{
				entity.Userid = (long?)db.GetParameterValue(cmd, "@USERID");
			}
			return res;
		}
		public static bool InsertListAppuser(List<Appuser> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				DbCommand cmd = GetInsertCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Appuser entity = list[i];
					SetInsertValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
					if (res)
					{
						entity.Userid = (long?)db.GetParameterValue(cmd, "@USERID");
					}
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool InsertListAppuser(List<Appuser> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetInsertCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				Appuser entity = list[i];
				SetInsertValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				if (res)
				{
					entity.Userid = (int?)db.GetParameterValue(cmd, "@USERID");
				}
			}
			return res;
		}
		#endregion
		#region Update
		private static DbCommand GetUpdateCommand(SqlDatabase db)
		{
            string updateSQL = "UPDATE dbo.AppUser SET ROLEID = @ROLEID, USERNAME = @USERNAME, PASSWORD = @PASSWORD, JMBG = @JMBG, FIRSTNAME = @FIRSTNAME, LASTNAME = @LASTNAME, STREETNAME = @STREETNAME, STREETNUMBER = @STREETNUMBER, POSTALCODE = @POSTALCODE, CITY = @CITY, EMAILADRESS = @EMAILADRESS, RECEIVEMAIL = @RECEIVEMAIL, PHONENO = @PHONENO, BIRTHDATE = @BIRTHDATE, STATUS = @STATUS WHERE USERID = @USERID";
            DbCommand updateCommand = db.GetSqlStringCommand(updateSQL);
            db.AddInParameter(updateCommand, "@USERID", DbType.Int64);
            db.AddInParameter(updateCommand, "@ROLEID", DbType.Int32);
            db.AddInParameter(updateCommand, "@USERNAME", DbType.String);
            db.AddInParameter(updateCommand, "@PASSWORD", DbType.String);
            db.AddInParameter(updateCommand, "@JMBG", DbType.String);
            db.AddInParameter(updateCommand, "@FIRSTNAME", DbType.String);
            db.AddInParameter(updateCommand, "@LASTNAME", DbType.String);
            db.AddInParameter(updateCommand, "@STREETNAME", DbType.String);
            db.AddInParameter(updateCommand, "@STREETNUMBER", DbType.String);
            db.AddInParameter(updateCommand, "@POSTALCODE", DbType.String);
            db.AddInParameter(updateCommand, "@CITY", DbType.String);
            db.AddInParameter(updateCommand, "@EMAILADRESS", DbType.String);
            db.AddInParameter(updateCommand, "@RECEIVEMAIL", DbType.Boolean);
            db.AddInParameter(updateCommand, "@PHONENO", DbType.String);
            db.AddInParameter(updateCommand, "@BIRTHDATE", DbType.DateTime);
            db.AddInParameter(updateCommand, "@STATUS", DbType.Int32);
            return updateCommand;
		}
		private static void SetUpdateValues(SqlDatabase db, DbCommand cmd, Appuser entity)
		{
			db.SetParameterValue(cmd, "@USERID", entity.Userid);
			db.SetParameterValue(cmd, "@ROLEID", entity.Roleid);
			db.SetParameterValue(cmd, "@USERNAME", entity.Username);
			db.SetParameterValue(cmd, "@PASSWORD", entity.Password);
			db.SetParameterValue(cmd, "@JMBG", entity.Jmbg);
			db.SetParameterValue(cmd, "@FIRSTNAME", entity.Firstname);
			db.SetParameterValue(cmd, "@LASTNAME", entity.Lastname);
			db.SetParameterValue(cmd, "@STREETNAME", entity.Streetname);
			db.SetParameterValue(cmd, "@STREETNUMBER", entity.Streetnumber);
			db.SetParameterValue(cmd, "@POSTALCODE", entity.Postalcode);
			db.SetParameterValue(cmd, "@CITY", entity.City);
			db.SetParameterValue(cmd, "@EMAILADRESS", entity.Emailadress);
            db.SetParameterValue(cmd, "@RECEIVEMAIL", entity.ReceiveMail);
			db.SetParameterValue(cmd, "@PHONENO", entity.Phoneno);
			db.SetParameterValue(cmd, "@BIRTHDATE", entity.Birthdate);
			db.SetParameterValue(cmd, "@STATUS", entity.Statusid);
		}
		public static bool UpdateAppuser(Appuser entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}
		public static bool UpdateAppuser(Appuser entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			return res;
		}
		public static bool UpdateListAppuser(List<Appuser> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				DbCommand cmd = GetUpdateCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Appuser entity = list[i];
					SetUpdateValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool UpdateListAppuser(List<Appuser> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetUpdateCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				Appuser entity = list[i];
				SetUpdateValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			}
			return res;
		}
		
		#endregion
		#region Save
		public static bool SaveAppuser(Appuser entity)
		{
			bool res = true;
			if (entity.Userid == null )
			{
				res = InsertAppuser(entity);
			}
			else
			{
				res = UpdateAppuser(entity);
			}
			return res;
		}
		public static bool SaveAppuser(Appuser entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			if (entity.Userid == null )
			{
				res = InsertAppuser(entity, transactionObj, db);
			}
			else
			{
				res = UpdateAppuser(entity, transactionObj, db);
			}
			return res;
		}
		public static bool SaveListAppuser(List<Appuser> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Appuser entity = list[i];
					res = res & SaveAppuser(entity, transactionObj, db);
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool SaveListAppuser(List<Appuser> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			for(int i=0; i<list.Count; i++)
			{
				Appuser entity = list[i];
				res = res & SaveAppuser(entity, transactionObj, db);
			}
			return res;
		}
		#endregion
		#region Delete
		private static DbCommand GetDeleteCommand(SqlDatabase db)
		{
            string deleteSQL = "DELETE FROM dbo.AppUser WHERE USERID = @USERID";
            DbCommand deleteCommand = db.GetSqlStringCommand(deleteSQL);
            db.AddInParameter(deleteCommand, "@USERID", DbType.Int64);
            return deleteCommand;
		}

        private static void SetDeleteValues(SqlDatabase db, DbCommand cmd, long userID)
        {
            db.SetParameterValue(cmd, "@USERID", userID);
        }

        public static bool DeleteAppuser(int userID)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = GetDeleteCommand(db);
            SetDeleteValues(db, cmd, userID);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }

        public static bool DeleteAppuser(Appuser entity)
		{
            return DeleteAppuser((int)entity.Userid);
		}

		public static bool DeleteListAppuser(List<Appuser> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				DbCommand cmd = GetDeleteCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Appuser entity = list[i];
					SetDeleteValues(db, cmd, (long)entity.Userid);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}

        public static bool DeleteListAppuser(List<long> list)
        {
            bool res = true;
            DbTransaction transactionObj = null;
            DbConnection conn = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
                DbCommand cmd = GetDeleteCommand(db);
                conn = db.CreateConnection();
                conn.Open();
                transactionObj = conn.BeginTransaction();
                for (int i = 0; i < list.Count; i++)
                {
                    db.SetParameterValue(cmd, "@USERID", list[i]);
                    res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
                }
                transactionObj.Commit();
                transactionObj.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                res = false;
                if (transactionObj != null)
                {
                    transactionObj.Rollback();
                    transactionObj.Dispose();
                }
                if (conn != null & conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }
                throw ex;
            }
            return res;
        }
		
		#endregion
		#region Select
        private static string selectSQL = "SELECT USERID,ROLEID,USERNAME,PASSWORD,JMBG,FIRSTNAME,LASTNAME,STREETNAME,STREETNUMBER,POSTALCODE,CITY,EMAILADRESS,RECEIVEMAIL,PHONENO,BIRTHDATE,STATUS FROM dbo.AppUser {0} {1}";

        private static List<Appuser> LoadAppusers(string sql)
        {
            List<Appuser> res = new List<Appuser>();
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
                reader = db.ExecuteReader(CommandType.Text, sql);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        Appuser entity = new Appuser();
                        entity.Userid = reader.IsDBNull(0) ? null : (int?)reader.GetInt64(0);
                        entity.Roleid = (Appuser.Role)reader.GetInt32(1);
                        entity.Username = reader.IsDBNull(2) ? null : (string)reader.GetString(2);
                        entity.Password = reader.IsDBNull(3) ? null : (string)reader.GetString(3);
                        entity.Jmbg = reader.IsDBNull(4) ? null : (string)reader.GetString(4);
                        entity.Firstname = reader.IsDBNull(5) ? null : (string)reader.GetString(5);
                        entity.Lastname = reader.IsDBNull(6) ? null : (string)reader.GetString(6);
                        entity.Streetname = reader.IsDBNull(7) ? null : (string)reader.GetString(7);
                        entity.Streetnumber = reader.IsDBNull(8) ? null : (string)reader.GetString(8);
                        entity.Postalcode = reader.IsDBNull(9) ? null : (string)reader.GetString(9);
                        entity.City = reader.IsDBNull(10) ? null : (string)reader.GetString(10);
                        entity.Emailadress = reader.IsDBNull(11) ? null : (string)reader.GetString(11);
                        entity.ReceiveMail = reader.GetBoolean(12);
                        entity.Phoneno = reader.IsDBNull(13) ? null : (string)reader.GetString(13);
                        entity.Birthdate = reader.IsDBNull(14) ? null : (DateTime?)reader.GetDateTime(14);
                        entity.Statusid = (Appuser.Status)reader.GetInt32(15);
                        res.Add(entity);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                throw ex;
            }
            return res;
        }

		public static List<Appuser> LoadAppusers(string where, string orderby)
		{
			string sql = String.Format(selectSQL, where, orderby);
            return LoadAppusers(sql);
		}

        public static List<Appuser> LoadHouseHoldUsers(long userID)
        {
            string sql = String.Format(@"SELECT USERID,ROLEID,USERNAME,PASSWORD,JMBG,FIRSTNAME,LASTNAME,STREETNAME,STREETNUMBER,POSTALCODE,CITY,EMAILADRESS,RECEIVEMAIL,PHONENO,BIRTHDATE,STATUS FROM HOUSEHOLD HH 
INNER JOIN HOUSEHOLD HH1 ON HH.HEADID = HH1.HEADID
INNER JOIN APPUSER AU ON HH.MEMBERID = AU.USERID
WHERE HH1.MEMBERID = {0}", userID);
            return LoadAppusers(sql);
        }

        public static List<long> GetUserIds(string criteria)
        {
            List<long> res = new List<long>();
            string selectSql = String.Format("SELECT USERID FROM dbo.AppUser {0}", criteria);
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
                reader = db.ExecuteReader(CommandType.Text, selectSql);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        long id = reader.GetInt64(0);
                        res.Add(id);
                    }
                    reader.Read();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                throw ex;
            }
            return res;
        }

		#endregion
	}
}
